package com.weixin.dao.impl;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.mchange.v2.c3p0.cfg.C3P0Config;

import com.weixin.dao.User_TbDAO;

import com.weixin.entity.User_Tb;

import com.weixin.util.C3p0Util;


public class User_TbDAOImpl implements User_TbDAO {

	@Override
	public List<User_Tb> seletct() throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * from user_tb";
		return C3p0Util.qr.query(sql, new BeanListHandler<User_Tb>(User_Tb.class));
	}
	@Override
	public int insert(User_Tb user_tb) throws SQLException {
		// TODO Auto-generated method stub
		Connection connection=C3p0Util.source.getConnection();
		String sql="insert into user_tb(head,user_name,password,email,telephone,brithday,gender,address,question1,answer1,question2,answer2,remark) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		C3p0Util.qr.update(connection,sql,user_tb.getHead(), user_tb.getUser_name(),user_tb.getPassword(),user_tb.getEmail(),user_tb.getTelephone(),user_tb.getBrithday(),user_tb.getGender(),user_tb.getAddress(),user_tb.getQuestion1(),user_tb.getAnswer1(),user_tb.getQuestion2(),user_tb.getAnswer2(),user_tb.getRemark());
		int b =((BigInteger) C3p0Util.qr.query(connection,"select @@identity",new ScalarHandler())).intValue(); 
		 connection.close();
		return b;
	}
	@Override
	public User_Tb getById(int id) throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * from user_tb where user_id=?";
		return C3p0Util.qr.query(sql, new BeanHandler<User_Tb>(User_Tb.class),id);
	}
	@Override
	public boolean update(User_Tb user_tb) throws SQLException {
		System.out.println("+++++++++++++++"+user_tb);
		// TODO Auto-generated method stub
		String sql="update user_tb set user_name=?,password=?,email=?,telephone=?,brithday=?,gender=?,address=?,question1=?,answer1=?, question2=?,answer2=?,remark=? where user_id=?";
		int update = C3p0Util.qr.update(sql, user_tb.getUser_name(),user_tb.getPassword(),user_tb.getEmail(),user_tb.getTelephone(),user_tb.getBrithday(),user_tb.getGender(),user_tb.getAddress(),user_tb.getQuestion1(),user_tb.getAnswer1(),user_tb.getQuestion2(),user_tb.getAnswer2(),user_tb.getRemark(),user_tb.getUser_id());
		return update>0;
	}
	@Override
	public boolean delete(int id) throws SQLException {
		// TODO Auto-generated method stub
		String sql="delete from user_tb where user_id=?";
		int update = C3p0Util.qr.update(sql, id);
		return update>0;
	}
	@Override
	public User_Tb selectmp(String name, String password) throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * from user_tb where user_name=? and password=?";
		return C3p0Util.qr.query(sql,new BeanHandler<User_Tb>(User_Tb.class),name,password);
	}
	@Override
	public List<User_Tb> listRole(String role) throws SQLException {
		// TODO Auto-generated method stub
		
		String sql="select * \r\n" + 
				"from user_tb\r\n" + 
				"where user_id in (\r\n" + 
				"      select user_id\r\n" + 
				"      from user_role_tb \r\n" + 
				"         where role_id in (\r\n" + 
				"	      select role_id\r\n" + 
				"	      from role_tb\r\n" + 
				"	      where title=?))";
		return C3p0Util.qr.query(sql, new BeanListHandler<User_Tb>(User_Tb.class),role);
	}
	@Override
	public boolean deleteAll(List<String> ids) throws SQLException {
		// TODO Auto-generated method stub
			StringBuffer sql=new StringBuffer("delete from user_tb where user_id in(");
			for (String string : ids) {
				sql.append("?,");
			}
			sql.deleteCharAt(sql.length()-1);
			sql.append(")");
			Object[] array = ids.toArray();
		return C3p0Util.qr.update(sql.toString(), array)>0;
	}
	@Override
	public List<User_Tb> selectAllByRolePagination(String role, int pageSize, int pageNum) throws SQLException {
		return C3p0Util.qr.query("select * from\r\n" + 
				 "user_tb where user_id in (select user_id from user_role_tb\r\n" + 
				 "where role_id in(select role_id \r\n" + 
				 "from role_tb\r\n" + 
				 "where title=?)) limit ?,?", new BeanListHandler<User_Tb>(User_Tb.class),role,(pageNum-1)*pageSize,pageSize);
	}
    
	@Override
	public int selectTotal(String role) throws SQLException {
		 return (int)(long)C3p0Util.qr.query("select count(*) from "+
							"user_tb where user_id in (select user_id from user_role_tb\r\n" + 
							"where role_id in(select role_id \r\n" + 
							"from role_tb\r\n" + 
							"where title=?))",new ScalarHandler(),role);
	}
	@Override
	public List<User_Tb> selectAllByRoleSearchPagination(String role, String search, int pageSize, int pageNum)
			throws SQLException {
		// TODO Auto-generated method stub
		String sql= "select * \r\n" + 
				"from (select user_tb.*,role_tb.title\r\n" + 
				"from user_tb left join user_role_tb\r\n" + 
				"on user_tb.user_id=user_role_tb.user_id\r\n" + 
				"left join role_tb\r\n" + 
				"on user_role_tb.role_id=role_tb.role_id)temp\r\n" + 
				"where title=? and concat(ifnull(user_id,''),ifnull(user_name,''),ifnull(brithday,''),ifnull(telephone,'')) like ? "+ 
				"limit ?,?";
		return C3p0Util.qr.query(sql,new BeanListHandler<User_Tb>(User_Tb.class),role,"%"+search+"%",(pageNum-1)*pageSize,pageSize);
		
	}
	@Override
	public int selectTotalBySearch(String role, String search) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select count(*)\r\n" + 
				"from (select user_tb.*,role_tb.title\r\n" + 
				"	from user_tb left join user_role_tb\r\n" + 
				"	on user_tb.user_id=user_role_tb.user_id\r\n" + 
				"	left join role_tb\r\n" + 
				"	on user_role_tb.role_id=role_tb.role_id)temp\r\n" + 
				"where title=? and concat(ifnull(user_id,''),ifnull(user_name,''),ifnull(brithday,''),ifnull(telephone,'')) like ?";
		return (int)(long)C3p0Util.qr.query(sql, new ScalarHandler(),role,"%"+search+"%");
	}
	@Override
	public User_Tb xiugai(String name) throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * from user_tb where user_name=? ";
		return C3p0Util.qr.query(sql, new BeanHandler<User_Tb>(User_Tb.class),name);
	}
	@Override
	public User_Tb yz(String question1, String answer1,String question2,String answer2,String name) throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * from user_tb where question1=? and answer1=? and question2=? and answer2=? and user_name=?";
		return C3p0Util.qr.query(sql, new BeanHandler<User_Tb>(User_Tb.class),question1,answer1,question2,answer2,name);
	}
	@Override
	public boolean pwdnew(User_Tb user_tb) throws SQLException {
		// TODO Auto-generated method stub
		String sql="update user_tb set password=? where user_name=?";
		int update = C3p0Util.qr.update(sql, user_tb.getPassword(),user_tb.getUser_name());
		return update>0;
	}

}
